package com.softeem.dao.impl;

import com.softeem.dao.CustomerDao;
import com.softeem.entity.Customer;
import com.softeem.utils.BaseDao;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;


public class CustomerDaoImpl implements CustomerDao {

    private QueryRunner qr = new QueryRunner(BaseDao.getDs());



    public Integer login(String name, String id) throws SQLException {

        String sql = "select count(id) from customer where name=? and id=?";

        Object query = qr.query(sql, new ScalarHandler<>(), name, id);

        return Integer.valueOf(query.toString());
    }



    public List<Customer> findAll() throws SQLException {

        String sql = "select * from customer limit 0,5";

        return qr.query(sql,new BeanListHandler<>(Customer.class));
    }



    public Integer delete(String id) throws SQLException {

        String sql = "delete from customer where id="+id;

        int update = qr.update(sql);

        return update;
    }



    public Integer total() throws SQLException {
        //总页数
        String sql = " select ceil(count(id)/5) from customer";

        Object query = qr.query(sql, new ScalarHandler<>());

        return Integer.valueOf(query.toString());
    }



    public List<Customer> findPages(String pageNo) throws SQLException {
        //根据页码进行分页查寻
        String sql = "select * from customer limit ?,5";
        //pageNo*5-5
        int page = Integer.parseInt(pageNo)*5-5;

        return qr.query(sql,new BeanListHandler<>(Customer.class),page);
    }


    public Customer findOne(String id) throws SQLException {
        String sql = "select * from customer where id="+id;
        return qr.query(sql,new BeanHandler<>(Customer.class));
    }


    public Integer update(Customer cus) throws SQLException {
        String sql = "update customer set male=?,birthday=?," +
                "town=?,phone=?,email=? where id="+cus.getId();
        int row = qr.update(sql, cus.getMale(), cus.getBirthday(), cus.getTown(), cus.getPhone(),
                cus.getEmail());

        return row;
    }


}
